package com.A1908.dao.impl;

import com.A1908.dao.FeastInfoDao;
import com.A1908.entity.FeastInfo;
import com.A1908.entity.PageEntity;
import com.A1908.entity.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class FeastInfoDaoImpl extends BaseDao implements FeastInfoDao {
    DaoUtil daoUtil = new DaoUtil();
    Connection connection = null;
    PreparedStatement pstmt = null;
    ResultSet rs = null;

    @Override
    public List<FeastInfo> getAllFeastInfo(User user) {
        connection = conn();
        String sql = "\n" +
                "SELECT\n" +
                "\tf.id,\n" +
                "\tf.feastName,\n" +
                "\tt.typeName,\n" +
                "\tf.feastDate,\n" +
                "\tu.username,\n" +
                "\tf.feastAddress,\n" +
                "\tf.isDelete,\n" +
                "\tf.feastCost \n" +
                "FROM\n" +
                "\tfeastinfo f\n" +
                "\t\n" +
                "\tINNER JOIN userfeasttype t ON f.feastType = t.id\n" +
                "\n" +
                "\tINNER JOIN `user` u ON f.feastUser = u.id\n" +
                "\tWHERE u.id=?;";
        Object[] objects = {user.getId()};
        pstmt = pstmt(connection, sql, objects);
        List<FeastInfo> list = new ArrayList<FeastInfo>();
        try {
            rs = pstmt.executeQuery();
            while (rs.next()) {
                FeastInfo info = new FeastInfo();
                info.setId(rs.getInt(1));
                info.setFeastName(rs.getString(2));
                info.setTypeName(rs.getString(3));
                info.setFeastDate(rs.getString(4));
                info.setUserName(rs.getString(5));
                info.setFeastAddress(rs.getString(6));
                info.setIsDelete(rs.getInt(7));
                info.setFeastCost(rs.getDouble(8));
                list.add(info);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            cloesAll(connection, pstmt, rs);
        }

        return null;
    }

    @Override
    public List<FeastInfo> getAllFeastInfo(User user, PageEntity pageEntity) {
        connection = conn();
        String sql = "select f.id,\n" +
                "f.feastName,\n" +
                "t.typeName,\n" +
                "f.feastDate,\n" +
                "u.username,\n" +
                "f.feastAddress,\n" +
                "f.isDelete,\n" +
                "f.feastCost,\n" +
                "f.feastType\n" +
                "from feastinfo f\n" +
                "inner join userfeasttype t on f.feastType = t.id\n" +
                "inner join user u on f.feastUser = u.id\n" +
                "where u.id = ? limit ?,?\n" +
                ";";
        int startNum = (pageEntity.getCurrentPage() - 1) * pageEntity.getPageSize();
        Object[] objects = {user.getId(), startNum, pageEntity.getPageSize()};
        pstmt = pstmt(connection, sql, objects);
        List<FeastInfo> list = new ArrayList<FeastInfo>();
        try {
            rs = pstmt.executeQuery();
            while (rs.next()) {
                FeastInfo info = new FeastInfo();
                info.setId(rs.getInt(1));
                info.setFeastName(rs.getString(2));
                info.setTypeName(rs.getString(3));
                info.setFeastDate(rs.getString(4));
                info.setUserName(rs.getString(5));
                info.setFeastAddress(rs.getString(6));
                info.setIsDelete(rs.getInt(7));
                info.setFeastCost(rs.getDouble(8));
                info.setFeastType(rs.getInt(9));
                list.add(info);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            cloesAll(connection, pstmt, rs);
        }
        return null;
    }

    @Override
    public List<FeastInfo> getFeastInfoByName(FeastInfo feastInfo, PageEntity pageEntity) {
        connection = conn();
        String sql = "SELECT f.id,f.feastName,t.typeName,f.feastDate,u.username,f.feastAddress,f.isDelete,f.feastCost,f.feastType\n" +
                "FROM feastinfo f\n" +
                "INNER JOIN userfeasttype t ON f.feastType = t.id\n" +
                "INNER JOIN `user` u ON f.feastUser = u.id\n" +
                "WHERE u.id = ?\n" +
                "AND f.feastName LIKE ?\n" +
                "AND f.feastType LIKE ?\n" +
                "and f.feastDate between ? and ?\n" +
                "LIMIT ?,?;";
        int startNum = (pageEntity.getCurrentPage() - 1) * pageEntity.getPageSize();

        String featType;
        if (feastInfo.getFeastType() == 0) {
            featType = "";
        } else {
            featType = Integer.toString(feastInfo.getFeastType());
        }

        String startDate = "";
        String endDate = "";

        if (feastInfo.getStartDateAndEndDate() != null && feastInfo.getStartDateAndEndDate().length > 1) {
            startDate = feastInfo.getStartDateAndEndDate()[0];
            endDate = feastInfo.getStartDateAndEndDate()[1];
        } else {
            startDate = "1753-01-01";
            endDate = "9999-12-31";
        }

        Object[] objects = {feastInfo.getFeastUser(), "%" + feastInfo.getFeastName() + "%", "%" + featType + "%", startDate, endDate, startNum, pageEntity.getPageSize()};
        pstmt = pstmt(connection, sql, objects);
        List<FeastInfo> list = new ArrayList<FeastInfo>();
        try {
            rs = pstmt.executeQuery();
            while (rs.next()) {
                FeastInfo info = new FeastInfo();
                info.setId(rs.getInt(1));
                info.setFeastName(rs.getString(2));
                info.setTypeName(rs.getString(3));
                info.setFeastDate(rs.getString(4));
                info.setUserName(rs.getString(5));
                info.setFeastAddress(rs.getString(6));
                info.setIsDelete(rs.getInt(7));
                info.setFeastCost(rs.getDouble(8));
                info.setFeastType(rs.getInt(9));
                list.add(info);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            cloesAll(connection, pstmt, rs);
        }
        return list;
    }

    @Override
    public int addFeastInfo(FeastInfo info) {
        String sql = "insert into feastinfo values (default,?,?,?,?,?,0,?,null)";
        Object[] objects = {
                info.getFeastName(),
                info.getFeastType(),
                info.getFeastDate(),
                info.getFeastUser(),
                info.getFeastAddress(),
                info.getFeastCost()
        };
        return executeUpdate(sql, objects);
    }

    @Override
    public int delFeastInfo(FeastInfo info, User user) {
        String sql = "delete from feastinfo where id = ? and feastUser = ?;";
        Object[] objects = {info.getId(), user.getId()};
        return executeUpdate(sql, objects);
    }

    public List<FeastInfo> getFeastAnalysis(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.id,\n" +
                "\tfeastinfo.feastDate,\n" +
                "\tfeastinfo.feastName,\n" +
                "\tfeastinfo.feastCost,\n" +
                "\tSUM(attendpeopleinfo.gift)\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON feastinfo.id = attendpeopleinfo.attendFeast \n" +
                "WHERE\n" +
                "\tfeastinfo.feastUser = ?\n" +
                "\tGROUP BY feastinfo.id;";
        connection = super.conn();
        Object[] objects = {user.getId()};
        pstmt = super.pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            List<FeastInfo> list = new ArrayList<>();
            while (rs.next()) {
                FeastInfo info = new FeastInfo();
                info.setId(rs.getInt(1));
                info.setFeastDate(rs.getString(2));
                info.setFeastName(rs.getString(3));
                info.setFeastCost(rs.getInt(4));
                info.setFeastGet(rs.getInt(5));
                list.add(info);
            }
            return list;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public FeastInfo getFeastAnalysisMaxCost(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.id,\n" +
                "\tfeastinfo.feastDate,\n" +
                "\tfeastinfo.feastName,\n" +
                "\tfeastinfo.feastCost,\n" +
                "\tSUM(attendpeopleinfo.gift)\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON feastinfo.id = attendpeopleinfo.attendFeast \n" +
                "WHERE\n" +
                "\tfeastinfo.feastUser = ?\n" +
                "\tGROUP BY feastinfo.id\n" +
                "\tORDER BY feastCost DESC;";
        connection = super.conn();
        Object[] objects = {user.getId()};
        pstmt = super.pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            FeastInfo info = new FeastInfo();
            if (rs.next()) {
                info.setId(rs.getInt(1));
                info.setFeastDate(rs.getString(2));
                info.setFeastName(rs.getString(3));
                info.setFeastCost(rs.getInt(4));
                info.setFeastGet(rs.getInt(5));
            }
            return info;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public FeastInfo getFeastAnalysisMinCost(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.id,\n" +
                "\tfeastinfo.feastDate,\n" +
                "\tfeastinfo.feastName,\n" +
                "\tfeastinfo.feastCost,\n" +
                "\tSUM(attendpeopleinfo.gift)\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON feastinfo.id = attendpeopleinfo.attendFeast \n" +
                "WHERE\n" +
                "\tfeastinfo.feastUser = ?\n" +
                "\tGROUP BY feastinfo.id\n" +
                "\tORDER BY feastCost ASC;";
        connection = super.conn();
        Object[] objects = {user.getId()};
        pstmt = super.pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            FeastInfo info = new FeastInfo();
            if (rs.next()) {
                info.setId(rs.getInt(1));
                info.setFeastDate(rs.getString(2));
                info.setFeastName(rs.getString(3));
                info.setFeastCost(rs.getInt(4));
                info.setFeastGet(rs.getInt(5));
            }
            return info;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public FeastInfo getFeastAnalysisMaxGet(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.id,\n" +
                "\tfeastinfo.feastDate,\n" +
                "\tfeastinfo.feastName,\n" +
                "\tfeastinfo.feastCost,\n" +
                "\tSUM(attendpeopleinfo.gift)\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON feastinfo.id = attendpeopleinfo.attendFeast \n" +
                "WHERE\n" +
                "\tfeastinfo.feastUser = ?\n" +
                "\tGROUP BY feastinfo.id\n" +
                "\tORDER BY \tSUM(attendpeopleinfo.gift) DESC;";
        connection = super.conn();
        Object[] objects = {user.getId()};
        pstmt = super.pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            FeastInfo info = new FeastInfo();
            if (rs.next()) {
                info.setId(rs.getInt(1));
                info.setFeastDate(rs.getString(2));
                info.setFeastName(rs.getString(3));
                info.setFeastCost(rs.getInt(4));
                info.setFeastGet(rs.getInt(5));
            }
            return info;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    public FeastInfo getFeastAnalysisMinGet(User user) {
        String sql = "SELECT\t\n" +
                "\tfeastinfo.id,\n" +
                "\tfeastinfo.feastDate,\n" +
                "\tfeastinfo.feastName,\n" +
                "\tfeastinfo.feastCost,\n" +
                "\tSUM(attendpeopleinfo.gift)\n" +
                "FROM\n" +
                "\tfeastinfo\n" +
                "\tINNER JOIN attendpeopleinfo ON feastinfo.id = attendpeopleinfo.attendFeast \n" +
                "WHERE\n" +
                "\tfeastinfo.feastUser = ?\n" +
                "\tGROUP BY feastinfo.id\n" +
                "\tORDER BY \tSUM(attendpeopleinfo.gift) ASC;";
        connection = super.conn();
        Object[] objects = {user.getId()};
        pstmt = super.pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            FeastInfo info = new FeastInfo();
            if (rs.next()) {
                info.setId(rs.getInt(1));
                info.setFeastDate(rs.getString(2));
                info.setFeastName(rs.getString(3));
                info.setFeastCost(rs.getInt(4));
                info.setFeastGet(rs.getInt(5));

            }
            return info;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateFeastInfo(FeastInfo info) {
        /*UPDATE feastinfo SET feastinfo.feastName = '管理员测试添加1',feastinfo.feastType = 7,feastinfo.feastDate = '2021-04-17',feastinfo.feastUser = 100000,feastinfo.feastAddress = '河南省郑州市中原区颖河路71号附近',feastinfo.isDelete = 0,feastinfo.feastCost = '2100' WHERE feastinfo.id = 13*/
        String sql = "UPDATE feastinfo SET feastinfo.feastName = ?,feastinfo.feastType = ?,feastinfo.feastDate = ?,feastinfo.feastUser = ?,feastinfo.feastAddress = ?,feastinfo.isDelete = ?,feastinfo.feastCost = ? WHERE feastinfo.id = ?";
        Object[] objects = {
                info.getFeastName(),
                info.getFeastType(),
                info.getFeastDate(),
                info.getFeastUser(),
                info.getFeastAddress(),
                info.getIsDelete(),
                info.getFeastCost(),
                info.getId()
        };
        return executeUpdate(sql, objects);
    }

    @Override
    public int getCountByName(FeastInfo info) {
        String sql = "SELECT count(*)\n" +
                "FROM feastinfo f\n" +
                "         INNER JOIN userfeasttype t ON f.feastType = t.id\n" +
                "         INNER JOIN `user` u ON f.feastUser = u.id\n" +
                "WHERE u.id = ?\n" +
                "  AND f.feastName LIKE ?\n" +
                "  AND f.feastType LIKE ?\n" +
                "  and f.feastDate between ? and ?;";
        connection = conn();
        String featType;
        if (info.getFeastType() == 0) {
            featType = "";
        } else {
            featType = Integer.toString(info.getFeastType());
        }

        String startDate = "";
        String endDate = "";

        if (info.getStartDateAndEndDate() != null && info.getStartDateAndEndDate().length > 1) {
            startDate = info.getStartDateAndEndDate()[0];
            endDate = info.getStartDateAndEndDate()[1];
        } else {
            startDate = "1753-01-01";
            endDate = "9999-12-31";
        }

        Object[] objects = {info.getFeastUser(), "%" + info.getFeastName() + "%", "%" + featType + "%", startDate, endDate};
        pstmt = pstmt(connection, sql, objects);
        try {
            rs = pstmt.executeQuery();
            if (rs.next()) {
                return rs.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            cloesAll(connection, pstmt, rs);
        }

        return 0;
    }

    @Override
    public int getCount(User user) {
        String sql = "SELECT\n" +
                "\tCOUNT(*) \n" +
                "FROM\n" +
                "\tfeastinfo f\n" +
                "\tINNER JOIN userfeasttype t ON f.feastType = t.id\n" +
                "\tINNER JOIN `user` u ON f.feastUser = u.id \n" +
                "WHERE\n" +
                "\tu.id = ?";
        return daoUtil.getCount(sql, user);
    }
}
